package com.db.jdbc;

import com.bean.PaginationSupport;
import com.bean.User;
import com.db.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zhi keyan
 * @create 2020-12-11 21:51
 */
@Repository
public class JdbcUserRepository implements UserRepository{

    private JdbcTemplate jdbc;

    private static final String SELECT_USER = "select userID, username, password, islock from user ";


    private static final String SELECT_PAGE_USER = SELECT_USER + " order by userID desc limit ? offset ?";

    private static final String SELECT_PAGE_LOCKUSER = SELECT_USER + " where isLock = true order by userID desc limit ? offset ?";

    private static final String SELECT_PAGE_UNLOCKUSER = SELECT_USER + " where isLock = false order by userID desc limit ? offset ?";

    private static final String UPDATE_USER = "update user set ";

    private static final String INSERT_USER = "insert into user (userID, username, password, isLock) values (?,?,?,?) ";

    private static final String MAX_USERID = "select max(userid) from user ";
    // 使用在字段上用于根据类型注入依赖
    @Autowired
    public JdbcUserRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    private static class UserRowMapper implements RowMapper<User>{
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new User(rs.getInt("userid"), rs.getString("username"),
                    rs.getString("password"), rs.getBoolean("islock"));
        }
    }
    @Override
    public User save(User user) {
        int userID = insertUserandReturnID(user);
        return new User(userID,user.getUserName(),user.getPassword(),false);
        //jdbc.update(INSERT_USER, maxuserid()+1,user.getUserName(), user.getPassWord(), false);
        //return user;
    }

    /**
     * 通过自增长生成userID
     * @param user
     * @return
     */
    private int insertUserandReturnID(User user){
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbc).withTableName("user");
        jdbcInsert.setGeneratedKeyName("userID");
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("username", user.getUserName());
        args.put("password", user.getPassword());
        args.put("isLock", false);
        int userID = jdbcInsert.executeAndReturnKey(args).intValue();
        return userID;
    }

    @Override
    public User findByUserName(String username) {
        User user = null;
        try{
            user = jdbc.queryForObject(SELECT_USER + " where username=? ", new UserRowMapper(), username);
        }catch(Exception e){

        }

        return user;
    }

    @Override
    public User findByUserName(String username, String password) {
        User user = null;
        try{
            user = jdbc.queryForObject(SELECT_USER + " where username=? and password=?", new UserRowMapper(), username,password);
        }catch(Exception e){
        }
        return user;
    }

    @Override
    public User findByUserID(int userID) {
        User user = null;
        try{
            user = jdbc.queryForObject(SELECT_USER + " where userID=?", new UserRowMapper(), userID);
        }catch (Exception e){

        }
        return user;
    }

    @Override
    public long countUser() {

        return jdbc.queryForLong("select count(userid) from user");
    }

    @Override
    public long countLockUser() {
        return jdbc.queryForLong("select count(userid) from user where isLock = true");
    }

    @Override
    public long countUnlockUser() {
        return jdbc.queryForLong("select count(userid) from user where isLock = false");
    }

    @Override
    public PaginationSupport<User> findAllUserPage(int pageNo, int pageSize) {

        int totalCount = (int) countUser();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<User>(new ArrayList<User>(0), 0);

        List<User> items = jdbc.query(SELECT_PAGE_USER , new UserRowMapper(), pageSize, startIndex);
        PaginationSupport<User> ps = new PaginationSupport<User>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    @Override
    public PaginationSupport<User> findLockUserPage(int pageNo, int pageSize) {
        int totalCount = (int) countLockUser();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<User>(new ArrayList<User>(0), 0);

        List<User> items = jdbc.query(SELECT_PAGE_LOCKUSER , new UserRowMapper(), pageSize, startIndex);
        PaginationSupport<User> ps = new PaginationSupport<User>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    @Override
    public PaginationSupport<User> findUnlockUserPage(int pageNo, int pageSize) {
        int totalCount = (int) countUnlockUser();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<User>(new ArrayList<User>(0), 0);

        List<User> items = jdbc.query(SELECT_PAGE_UNLOCKUSER, new UserRowMapper(), pageSize, startIndex);
        PaginationSupport<User> ps = new PaginationSupport<User>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    @Override
    public int maxuserid() {
        return jdbc.queryForInt(MAX_USERID);
    }

    @Override
    public void changeLock(User locked) {

        jdbc.update(UPDATE_USER + " isLock = ? where userID = ? " , !locked.getLock() , locked.getUserID() );

    }
}
